﻿using Model;
using System;
using System.Data;

namespace BLL
{
    public class Qs_Week_SonBLL : BaseBLL
    {
        #region 新增

        public int Insert(Qs_Week_Son s, string operatename)
        {
            s.S_id = GetSequence("QS_WEEK_SON_ID");
            string sql = @"insert into Qs_Week_Son ( s_id,
        mian_id,
        s_user_id,
        operatortime,
        endlastweekfund,
        endlastweekusefund,
        tswkfund,
        n_fundid,
        m_id)

values ({8},{0},{1},to_date('{2}','yyyy-mm-dd hh24:mi:ss'),{3},{4},{5},{6},{7})";
            sql = string.Format(sql, s.Mian_id, s.S_user_id, s.Operatortime, s.Endlastweekfund, s.Endlastweekusefund, s.Tswkfund, s.N_fundid, s.M_id, s.S_id);
            int count = NoQuery(sql, operatename);
            return count;
        }

        /// <summary>
        /// 查询实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Qs_Week_Son Find(decimal id)
        {
            var sql = "SELECT S_ID,MIAN_ID,S_USER_ID,OPERATORTIME,ENDLASTWEEKFUND,ENDLASTWEEKUSEFUND,TSWKFUND,N_FUNDID,M_ID FROM QS_WEEK_SON WHERE S_ID = {0}";
            sql = string.Format(sql, id);
            var row = RowSingeleOfNull(sql);
            if (row == null)
            {
                return null;
            }
            var entity = new Qs_Week_Son
            {
                S_id = Convert.ToDecimal(row["S_ID"]),
                Mian_id = Convert.ToDecimal(row["MIAN_ID"]),
                S_user_id = Convert.ToDecimal(row["S_USER_ID"]),
                Operatortime = Convert.ToDateTime(row["OPERATORTIME"]),
                Endlastweekfund = Convert.ToDecimal(row["ENDLASTWEEKFUND"]),
                Endlastweekusefund = Convert.ToDecimal(row["ENDLASTWEEKUSEFUND"]),
                Tswkfund = Convert.ToDecimal(row["TSWKFUND"]),
                N_fundid = Convert.ToDecimal(row["N_FUNDID"]),
                M_id = Convert.ToDecimal(row["M_ID"])
            };
            return entity;
        }

        /// <summary>
        /// 通过主表ID 和 项目ID 查询实体
        /// </summary>
        /// <param name="main_id"></param>
        /// <param name="n_fund_id"></param>
        /// <returns></returns>
        public Qs_Week_Son Find(decimal main_id, decimal n_fund_id)
        {
            var sql = "SELECT S_ID,MIAN_ID,S_USER_ID,OPERATORTIME,ENDLASTWEEKFUND,ENDLASTWEEKUSEFUND,TSWKFUND,N_FUNDID,M_ID FROM QS_WEEK_SON WHERE MIAN_ID = {0} AND N_FUNDID = {1}";
            sql = string.Format(sql, main_id, n_fund_id);
            var row = RowSingeleOfNull(sql);
            if (row == null)
            {
                return null;
            }
            var entity = new Qs_Week_Son
            {
                S_id = Convert.ToDecimal(row["S_ID"]),
                Mian_id = Convert.ToDecimal(row["MIAN_ID"]),
                S_user_id = Convert.ToDecimal(row["S_USER_ID"]),
                Operatortime = Convert.ToDateTime(row["OPERATORTIME"]),
                Endlastweekfund = Convert.ToDecimal(row["ENDLASTWEEKFUND"]),
                Endlastweekusefund = Convert.ToDecimal(row["ENDLASTWEEKUSEFUND"]),
                Tswkfund = Convert.ToDecimal(row["TSWKFUND"]),
                N_fundid = Convert.ToDecimal(row["N_FUNDID"]),
                M_id = Convert.ToDecimal(row["M_ID"])
            };
            return entity;
        }

        #endregion 新增

        #region 明细

        public DataTable SelectReport(string companyId, string moth, string week)
        {
            string str = "select * from qs_week_main w where w.m_month='" + moth + "' and w.issave=1 ";

            string sql = "";
            DataTable dtWeek = Query(str);
            if (dtWeek == null || dtWeek.Rows.Count < 1)
            {
                return null;
            }
            else
            {
                if (week == "1")
                {
                    sql = @"select 公司名称, 项目名称, 截止上周资金计划, 截止上周累计资金, 本周资金计划,父级编号
  from (select f.pro_id 项目编号,
               CASE
                 when s.m_id = 0 then
                  (select fd.pro_name
                     from qs_fundplan fd
                    where fd.pro_id = f.pro_id)
                 else
                  (select fm.projectname
                     from qs_fundmonplan fm
                    where fm.id = s.m_id)
               end 项目名称,
               m.n_company_id 公司编号,
               c.cmy_name 公司名称,
               nvl(sum(s.tswkfund),0)  本周资金计划,
               s.m_id,
               decode(s.m_id, 0, 0, 0) 截止上周资金计划,
               nvl(sum(s.endlastweekusefund),0) 截止上周累计资金,
              f.fatherid 父级编号
          from qs_week_son s,
               qs_week_main      m,
               qs_fundplan       f,
               sys_company       c
         where s.mian_id = m.w_id
           and s.n_fundid = f.pro_id
           and m.n_company_id = c.cmy_id
           and m.issave = 1
           and m.n_company_id in
               (" + companyId + @")
           and m.m_month = '" + moth.Trim() + @"' and m.week_num='" + moth + @"1'
         group by f.pro_id,s.m_id, f.pro_name, m.n_company_id, c.cmy_name,f.fatherid
         order by m.n_company_id, f.pro_id)";
                }
                else
                {
                    int wk = Convert.ToInt32(week) - 1;
                    sql = @"select 公司名称, 项目名称, 截止上周资金计划, 截止上周累计资金, 本周资金计划,父级编号
  from (select decode(fa.公司名称, null, fs.公司名称, fa.公司名称) 公司名称,
               decode(fs.项目名称, null, fa.项目名称, fs.项目名称) 项目名称,
               nvl(截止上周资金计划, 0) 截止上周资金计划,
               nvl(decode(fs.截止上周累计资金, null,fa.截止上周累计资金),0) 截止上周累计资金,
               nvl(fs.本周资金计划, 0) 本周资金计划,
               decode(fa.项目编号, null, fs.项目编号, fa.项目编号) 项目编号,
               decode(fs.公司编号, null, fa.公司编号, fs.公司编号) 公司编号,
               decode(fs.m_id,null,fa.m_id,fs.m_id) m_id,
               decode(fs.fatherid,null,fa.fatherid,fs.fatherid) 父级编号
          from (

          select f.pro_id 项目编号,
                       CASE
                         when s.m_id = 0 then
                          (select fd.pro_name
                             from qs_fundplan fd
                            where fd.pro_id = f.pro_id)
                         else
                          (select fm.projectname
                             from qs_fundmonplan fm
                            where fm.id = s.m_id)
                       end 项目名称,
                       m.n_company_id 公司编号,
                       c.cmy_name 公司名称,
                       sum(s.endlastweekusefund) 截止上周累计资金,

                       sum(s.tswkfund) 本周资金计划,
                       s.m_id,
                      f.fatherid
                  from qs_week_son s,
                       qs_week_main      m,
                       qs_fundplan       f,
                       sys_company       c
                 where s.mian_id = m.w_id
                   and s.n_fundid = f.pro_id
                   and m.n_company_id = c.cmy_id
                   and m.issave = 1
                   and m.n_company_id in
                       (" + companyId + @")
                   and m.m_month = '" + moth + @"' and m.week_num='" + (moth + week) + @"'
                 group by f.pro_id,
                          s.m_id,
                          f.pro_name,
                          m.n_company_id,
                          c.cmy_name,
                      f.fatherid
                 order by m.n_company_id, f.pro_id

                 ) fs
          full join

         (
         select f.pro_id 项目编号,
                CASE
                  when s.m_id = 0 then
                   (select fd.pro_name
                      from qs_fundplan fd
                     where fd.pro_id = f.pro_id)
                  else
                   (select fm.projectname
                      from qs_fundmonplan fm
                     where fm.id = s.m_id)
                end 项目名称,
                m.n_company_id 公司编号,
                c.cmy_name 公司名称,
                sum(s.endlastweekusefund) 截止上周累计资金,
                sum(s.tswkfund) 截止上周资金计划,
                s.m_id,
                f.fatherid
           from qs_week_son s,
                qs_week_main      m,
                qs_fundplan       f,
                sys_company       c
          where s.mian_id = m.w_id
            and s.n_fundid = f.pro_id
            and m.n_company_id = c.cmy_id
            and m.issave = 1
            and m.n_company_id in
                (" + companyId + @")
            and m.m_month ='" + moth + @"' and m.week_num between '" + moth + @"1' and '" + (moth + wk.ToString()) + @"'
          group by f.pro_id, s.m_id, f.pro_name, m.n_company_id, c.cmy_name,f.fatherid
          order by m.n_company_id, f.pro_id

          ) fa
            on fs.项目编号 = fa.项目编号
           and fs.m_id = fa.m_id
           and fs.项目名称 = fa.项目名称
           and fs.公司编号 = fa.公司编号
         order by fs.项目编号

         )
 order by 公司编号, 项目编号,m_id";
                }
            }
            DataTable dt = Query(sql);
            return dt;
        }

        #endregion 明细

        #region 待办信息详细信息

        /// <summary>
        /// 待办信息详细信息
        /// </summary>
        /// <param name="businessId"></param>
        /// <returns></returns>
        public DataTable Pending(string businessId)
        {
            string sql = @"select f.pro_id,case when s.m_id=0 then
(select ff.pro_name from qs_fundplan ff where ff.pro_id=f.pro_id)
else
  (select fm.projectname from qs_fundmonplan fm where fm.id=s.m_id)
  end pro_name,
  sum(s.endlastweekusefund) endlastweekusefund,
  sum(s.tswkfund) tswkfund,
  s.m_id

 from qs_week_main m,qs_week_son s,qs_fundplan f
where m.w_id=s.mian_id
and f.pro_id=s.n_fundid

and m.n_business=" + businessId + @"

group by f.pro_id,pro_name,s.m_id

order by f.pro_id,s.m_id";
            return Query(sql);
        }

        #endregion 待办信息详细信息

        public DataTable findUser(string business)
        {
            string sql = @"select u.user_name,m.d_operator_time from qs_week_main m,sys_user u

where m.n_user_id=u.user_id
and m.n_business=" + business;
            return Query(sql);
        }

        public DataTable Print(string Business)
        {
            string com = "select w.n_company_id from qs_week_main w where w.n_business=" + Business;
            DataTable dt = Query(com);
            string companyId = "";
            if (dt != null && dt.Rows.Count > 0)
            {
                companyId = dt.Rows[0][0].ToString();
            }

            string sql = @"select 公司名称,
       项目名称,
       截止上周资金计划,
       截止上周累计资金,
       本周资金计划,
       父级编号
  from (select decode(fa.公司名称, null, fs.公司名称, fa.公司名称) 公司名称,
               decode(fs.项目名称, null, fa.项目名称, fs.项目名称) 项目名称,
               nvl(截止上周资金计划, 0) 截止上周资金计划,
               nvl(fs.截止上周累计资金, 0) 截止上周累计资金,
               nvl(fs.本周资金计划, 0) 本周资金计划,
               decode(fa.项目编号, null, fs.项目编号, fa.项目编号) 项目编号,
               decode(fs.公司编号, null, fa.公司编号, fs.公司编号) 公司编号,
               decode(fs.m_id, null, fa.m_id, fs.m_id) m_id,
               decode(fs.fatherid, null, fa.fatherid, fs.fatherid) 父级编号
          from (

                select f.pro_id 项目编号,
                        CASE
                          when s.m_id = 0 then
                           (select fd.pro_name
                              from qs_fundplan fd
                             where fd.pro_id = f.pro_id)
                          else
                           (select fm.projectname
                              from qs_fundmonplan fm
                             where fm.id = s.m_id)
                        end 项目名称,
                        m.n_company_id 公司编号,
                        c.cmy_name 公司名称,
                        sum(s.endlastweekusefund) 截止上周累计资金,
                        sum(s.tswkfund) 本周资金计划,
                        s.m_id,
                        f.fatherid
                  from qs_week_son  s,
                        qs_week_main m,
                        qs_fundplan  f,
                        sys_company  c
                 where s.mian_id = m.w_id
                   and s.n_fundid = f.pro_id
                   and m.n_company_id = c.cmy_id
                   and m.issave = 1";
            if (companyId.Trim().ToString() != "")
            {
                sql += " and m.n_company_id in (" + companyId + ")";
            }

            sql += @"
                   and m.n_user_id=(select qsm.n_user_id from qs_week_main qsm where qsm.n_business=" + Business + @")
                   and m.m_month = (select wm.m_month
                                      from qs_week_main wm
                                     where wm.n_business = " + Business + @")
                   and m.week_num = (select wm.week_num
                                       from qs_week_main wm
                                      where wm.n_business = " + Business + @")
                 group by f.pro_id,
                           s.m_id,
                           f.pro_name,
                           m.n_company_id,
                           c.cmy_name,
                           f.fatherid
                 order by m.n_company_id, f.pro_id

                ) fs
          full join

         (select f.pro_id 项目编号,
                CASE
                  when s.m_id = 0 then
                   (select fd.pro_name
                      from qs_fundplan fd
                     where fd.pro_id = f.pro_id)
                  else
                   (select fm.projectname
                      from qs_fundmonplan fm
                     where fm.id = s.m_id)
                end 项目名称,
                m.n_company_id 公司编号,
                c.cmy_name 公司名称,
                sum(s.tswkfund) 截止上周资金计划,
                s.m_id,
                f.fatherid
           from qs_week_son s, qs_week_main m, qs_fundplan f, sys_company c
          where s.mian_id = m.w_id
            and s.n_fundid = f.pro_id
            and m.n_company_id = c.cmy_id
            and m.issave = 1";
            if (companyId.Trim().ToString() != "")
            {
                sql += " and m.n_company_id in (" + companyId + ")";
            }

            sql += @"
            and m.n_user_id=(select qsm.n_user_id from qs_week_main qsm where qsm.n_business=" + Business + @")
            and m.m_month = (select wm.m_month
                               from qs_week_main wm
                              where wm.n_business = " + Business + @")
            and m.week_num >= (select wm.m_month || 1
                                 from qs_week_main wm
                                where wm.n_business = " + Business + @")
            and m.week_num < (select fkm.week_num
                                from qs_week_main fkm
                               where fkm.n_business = " + Business + @")
          group by f.pro_id,
                   s.m_id,
                   f.pro_name,
                   m.n_company_id,
                   c.cmy_name,
                   f.fatherid
          order by m.n_company_id, f.pro_id

         ) fa
            on fs.项目编号 = fa.项目编号
           and fs.m_id = fa.m_id
           and fs.项目名称 = fa.项目名称
           and fs.公司编号 = fa.公司编号
         order by fs.项目编号

        )
 order by 公司编号, 项目编号, m_id";
            return Query(sql);
        }

        public DataTable ApplyPrint(decimal wid)
        {
            string sql = "";
            sql += @"select 公司名称,
       项目名称,
       截止上周资金计划,
       截止上周累计资金,
       本周资金计划,
       父级编号
  from (select decode(fa.公司名称, null, fs.公司名称, fa.公司名称) 公司名称,
               decode(fs.项目名称, null, fa.项目名称, fs.项目名称) 项目名称,
               nvl(截止上周资金计划, 0) 截止上周资金计划,
               nvl(fs.截止上周累计资金, 0) 截止上周累计资金,
               nvl(fs.本周资金计划, 0) 本周资金计划,
               decode(fa.项目编号, null, fs.项目编号, fa.项目编号) 项目编号,
               decode(fs.公司编号, null, fa.公司编号, fs.公司编号) 公司编号,
               decode(fs.m_id, null, fa.m_id, fs.m_id) m_id,
               decode(fs.fatherid, null, fa.fatherid, fs.fatherid) 父级编号
          from (

                select f.pro_id 项目编号,
                        CASE
                          when s.m_id = 0 then
                           (select fd.pro_name
                              from qs_fundplan fd
                             where fd.pro_id = f.pro_id)
                          else
                           (select fm.projectname
                              from qs_fundmonplan fm
                             where fm.id = s.m_id)
                        end 项目名称,
                        m.n_company_id 公司编号,
                        c.cmy_name 公司名称,
                        sum(s.endlastweekusefund) 截止上周累计资金,
                        sum(s.tswkfund) 本周资金计划,
                        s.m_id,
                        f.fatherid
                  from qs_week_son  s,
                        qs_week_main m,
                        qs_fundplan  f,
                        sys_company  c
                 where s.mian_id = m.w_id
                   and s.n_fundid = f.pro_id
                   and m.n_company_id = c.cmy_id
                   and m.issave = 1
 and m.n_user_id=(select qsm.n_user_id from qs_week_main qsm where qsm.w_id=" + wid + @")
                   and m.m_month = (select wm.m_month
                                      from qs_week_main wm
                                     where wm.w_id = " + wid + @")
                   and m.week_num = (select wm.week_num
                                       from qs_week_main wm
                                      where wm.w_id = " + wid + @")
                   and m.n_company_id =
                       (select fcom.n_company_id
                          from qs_week_main fcom
                         where fcom.w_id = " + wid + @")
                 group by f.pro_id,
                           s.m_id,
                           f.pro_name,
                           m.n_company_id,
                           c.cmy_name,
                           f.fatherid
                 order by m.n_company_id, f.pro_id

                ) fs
          full join

         (select f.pro_id 项目编号,
                CASE
                  when s.m_id = 0 then
                   (select fd.pro_name
                      from qs_fundplan fd
                     where fd.pro_id = f.pro_id)
                  else
                   (select fm.projectname
                      from qs_fundmonplan fm
                     where fm.id = s.m_id)
                end 项目名称,
                m.n_company_id 公司编号,
                c.cmy_name 公司名称,
                sum(s.tswkfund) 截止上周资金计划,
                s.m_id,
                f.fatherid
           from qs_week_son s, qs_week_main m, qs_fundplan f, sys_company c
          where s.mian_id = m.w_id
            and s.n_fundid = f.pro_id
            and m.n_company_id = c.cmy_id
            and m.issave = 1
 and m.n_user_id=(select qsm.n_user_id from qs_week_main qsm where qsm.w_id=" + wid + @")
            and m.m_month =
                (select wm.m_month from qs_week_main wm where wm.w_id = " + wid + @")
            and m.week_num >= (select wm.m_month || 1
                                 from qs_week_main wm
                                where wm.w_id = " + wid + @")
            and m.week_num < (select fkm.week_num
                                from qs_week_main fkm
                               where fkm.w_id = " + wid + @")
            and m.n_company_id = (select fcom.n_company_id
                                    from qs_week_main fcom
                                   where fcom.w_id = " + wid + @")
          group by f.pro_id,
                   s.m_id,
                   f.pro_name,
                   m.n_company_id,
                   c.cmy_name,
                   f.fatherid
          order by m.n_company_id, f.pro_id

         ) fa
            on fs.项目编号 = fa.项目编号
           and fs.m_id = fa.m_id
           and fs.项目名称 = fa.项目名称
           and fs.公司编号 = fa.公司编号
         order by fs.项目编号

        )
 order by 公司编号, 项目编号, m_id ";
            return Query(sql);
        }

        public int Update(string Sid, string MainId, string Jin_e, string operatename)
        {
            var entity = Find(Convert.ToDecimal(Sid));
            if (entity != null)
            {
                if (entity.Mian_id == Convert.ToDecimal(MainId))
                {
                    entity.Endlastweekusefund = Convert.ToDecimal(Jin_e);
                    if (Update(entity, operatename))
                    {
                        return 1;
                    }
                }
            }
            return 0;
            /*
            string sql = "Update  qs_week_son s set s.endlastweekusefund=" + Jin_e + " where s.s_id=" + Sid + " and s.mian_id = " + MainId;
            int count = NoQuery(sql, operatename);
            return count;
            */
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="operatename"></param>
        /// <returns></returns>
        public bool Update(Qs_Week_Son entity, string operatename)
        {
            var sql = @"UPDATE QS_WEEK_SON
                        SET
                            MIAN_ID = {1},
                            S_USER_ID = {2},
                            OPERATORTIME = TO_DATE('{3}','YYYY-MM-DD HH24:MI:SS'),
                            ENDLASTWEEKFUND = {4},
                            ENDLASTWEEKUSEFUND = {5},
                            TSWKFUND = {6},
                            N_FUNDID = {7},
                            M_ID = {8}
                        WHERE
                            S_ID = {0}";
            sql = string.Format(sql,
                entity.S_id,
                entity.Mian_id,
                entity.S_user_id,
                entity.Operatortime.ToString("yyyy-MM-dd HH:mm:ss"),
                entity.Endlastweekfund,
                entity.Endlastweekusefund,
                entity.Tswkfund,
                entity.N_fundid,
                entity.M_id
                );
            if (NoQuery(sql, operatename) > 0)
            {
                return true;
            }
            return false;
        }
    }
}